package leaveSystem.dao.mysql;

import leaveSystem.dao.DAOException;
import leaveSystem.dao.LeaveDAO;
import leaveSystem.model.Leave;
import leaveSystem.utility.DateUtility;

import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.math.*;

public class LeaveDAOImpl implements LeaveDAO {

	private DateUtility dateObj;
	    public Leave createValueObject() {
	          return new Leave();
	    }
/*
	    public Leave getObject(Connection conn, int leaveId) throws DAOException, SQLException {

	          Leave valueObject = createValueObject();
	          valueObject.setLeaveId(leaveId);
	          load(conn, valueObject);
	          return valueObject;
	    }

	    public void load(Connection conn, Leave valueObject) throws DAOException, SQLException {

	          String sql = "SELECT * FROM leave WHERE (leave_id = ? ) "; 
	          PreparedStatement stmt = null;

	          try {
	               stmt = conn.prepareStatement(sql);
	               stmt.setInt(1, valueObject.getLeaveId()); 

	               singleQuery(conn, stmt, valueObject);

	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }
*/

	    public List<Leave> loadAll(Connection conn) throws SQLException {

	          String sql = "SELECT * FROM leaveapp.leave ORDER BY leave_id ASC ";
	          List<Leave> searchResults = listQuery(conn, conn.prepareStatement(sql));

	          return searchResults;
	    }

	    public void insertLeave(Connection conn, Leave valueObject) throws SQLException,Exception {

	          String sql = "";
	          PreparedStatement stmt = null;
	          ResultSet result = null;
	          dateObj = new DateUtility(); 

	          try {
	               sql = "INSERT INTO leaveapp.leave( leave_id, leave_type_id, employee_id, "
	               + "leave_startDate, leave_endDate, leave_reason, "
	               + "status, remarks, remaining_leaves, "
	               + "createdBy, createdOn, modifiedBy, "
	               + "modifiedOn) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
	               stmt = conn.prepareStatement(sql);

	               stmt.setInt(1, valueObject.getLeaveId()); 
	               stmt.setInt(2, valueObject.getLeaveTypeId()); 
	               stmt.setInt(3, valueObject.getEmployeeId()); 
	               stmt.setDate(4, dateObj.ConvertUtilToSql(valueObject.getLeaveStartDate()) ); 
	               stmt.setDate(5, (Date) dateObj.ConvertUtilToSql(valueObject.getLeaveEndDate())); 
	               stmt.setString(6, valueObject.getLeaveReason()); 
	               stmt.setString(7, valueObject.getStatus()); 
	               stmt.setString(8, valueObject.getRemarks()); 
	               stmt.setInt(9, valueObject.getRemainingLeaves()); 
	               stmt.setString(10, valueObject.getCreatedBy()); 
	               stmt.setDate(11,dateObj.ConvertUtilToSql(valueObject.getCreatedOn()) ); 
	               stmt.setString(12, valueObject.getModifiedBy()); 
	               stmt.setDate(13, dateObj.ConvertUtilToSql(valueObject.getModifiedOn())); 

	               int rowcount = databaseUpdate(conn, stmt);
	               if (rowcount != 1) {
	                    //System.out.println("PrimaryKey Error when updating DB!");
	                    throw new SQLException("PrimaryKey Error when updating DB!");
	               }

	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }


	    }

	    public void updateLeave(Connection conn, Leave valueObject)throws DAOException, Exception {

	          String sql = "UPDATE leaveapp.leave SET leave_type_id = ?, employee_id = ?, leave_startDate = ?, "
	               + "leave_endDate = ?, leave_reason = ?, status = ?, "
	               + "remarks = ?, remaining_leaves = ?, createdBy = ?, "
	               + "createdOn = ?, modifiedBy = ?, modifiedOn = ? WHERE (leave_id = ? ) ";
	          PreparedStatement stmt = null;
	     DateUtility date = new DateUtility();
	          try {
	              stmt = conn.prepareStatement(sql);
	              stmt.setInt(1, valueObject.getLeaveTypeId()); 
	              stmt.setInt(2, valueObject.getEmployeeId()); 
	              stmt.setDate(3, date.ConvertUtilToSql(valueObject.getLeaveStartDate())); 
	              stmt.setDate(4, date.ConvertUtilToSql(valueObject.getLeaveEndDate())); 
	              stmt.setString(5, valueObject.getLeaveReason()); 
	              stmt.setString(6, valueObject.getStatus()); 
	              stmt.setString(7, valueObject.getRemarks()); 
	              stmt.setInt(8, valueObject.getRemainingLeaves()); 
	              stmt.setString(9, valueObject.getCreatedBy()); 
	              stmt.setDate(10,  date.ConvertUtilToSql(valueObject.getCreatedOn())); 
	              stmt.setString(11, valueObject.getModifiedBy()); 
	              stmt.setDate(12, date.ConvertUtilToSql(valueObject.getModifiedOn())); 

	              stmt.setInt(13, valueObject.getLeaveId()); 

	              int rowcount = databaseUpdate(conn, stmt);
	              if (rowcount == 0) {
	                   //System.out.println("Object could not be saved! (PrimaryKey not found)");
	                   throw new DAOException("Object could not be saved! (PrimaryKey not found)");
	              }
	              if (rowcount > 1) {
	                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
	                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
	              }
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }

	    public void deleteLeave(Connection conn, Leave valueObject) throws DAOException, SQLException {

	          String sql = "DELETE FROM leaveapp.leave WHERE (leave_id = ? ) ";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              stmt.setInt(1, valueObject.getLeaveId()); 

	              int rowcount = databaseUpdate(conn, stmt);
	              if (rowcount == 0) {
	                   //System.out.println("Object could not be deleted (PrimaryKey not found)");
	                   throw new DAOException("Object could not be deleted! (PrimaryKey not found)");
	              }
	              if (rowcount > 1) {
	                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
	                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
	              }
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }

	    public void deleteAllLeave(Connection conn) throws SQLException {

	          String sql = "DELETE FROM leaveapp.leave";
	          PreparedStatement stmt = null;

	          try {
	              stmt = conn.prepareStatement(sql);
	              int rowcount = databaseUpdate(conn, stmt);
	          } finally {
	              if (stmt != null)
	                  stmt.close();
	          }
	    }
/*
	    public int countAll(Connection conn) throws SQLException {

	          String sql = "SELECT count(*) FROM leave";
	          PreparedStatement stmt = null;
	          ResultSet result = null;
	          int allRows = 0;

	          try {
	              stmt = conn.prepareStatement(sql);
	              result = stmt.executeQuery();

	              if (result.next())
	                  allRows = result.getInt(1);
	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }
	          return allRows;
	    }
*/
	    
	    public List<Leave> findLeave(Connection conn, Leave valueObject) throws SQLException {

	          List<Leave> searchResults;

	          boolean first = true;
	          StringBuffer sql = new StringBuffer("SELECT * FROM leaveapp.leave WHERE 1=1 ");

	          if (valueObject.getLeaveId() != 0) {
	              if (first) { first = false; }
	              sql.append("AND leave_id = ").append(valueObject.getLeaveId()).append(" ");
	          }

	          if (valueObject.getLeaveTypeId() != 0) {
	              if (first) { first = false; }
	              sql.append("AND leave_type_id = ").append(valueObject.getLeaveTypeId()).append(" ");
	          }

	          if (valueObject.getEmployeeId() != 0) {
	              if (first) { first = false; }
	              sql.append("AND employee_id = ").append(valueObject.getEmployeeId()).append(" ");
	          }

	          if (valueObject.getLeaveStartDate() != null) {
	              if (first) { first = false; }
	              sql.append("AND leave_startDate = '").append(valueObject.getLeaveStartDate()).append("' ");
	          }

	          if (valueObject.getLeaveEndDate() != null) {
	              if (first) { first = false; }
	              sql.append("AND leave_endDate = '").append(valueObject.getLeaveEndDate()).append("' ");
	          }

	          if (valueObject.getLeaveReason() != null) {
	              if (first) { first = false; }
	              sql.append("AND leave_reason LIKE '").append(valueObject.getLeaveReason()).append("%' ");
	          }

	          if (valueObject.getStatus() != null) {
	              if (first) { first = false; }
	              sql.append("AND status LIKE '").append(valueObject.getStatus()).append("%' ");
	          }

	          if (valueObject.getRemarks() != null) {
	              if (first) { first = false; }
	              sql.append("AND remarks LIKE '").append(valueObject.getRemarks()).append("%' ");
	          }

	          if (valueObject.getRemainingLeaves() != 0) {
	              if (first) { first = false; }
	              sql.append("AND remaining_leaves = ").append(valueObject.getRemainingLeaves()).append(" ");
	          }

	          if (valueObject.getCreatedBy() != null) {
	              if (first) { first = false; }
	              sql.append("AND createdBy LIKE '").append(valueObject.getCreatedBy()).append("%' ");
	          }

	          if (valueObject.getCreatedOn() != null) {
	              if (first) { first = false; }
	              sql.append("AND createdOn = '").append(valueObject.getCreatedOn()).append("' ");
	          }

	          if (valueObject.getModifiedBy() != null) {
	              if (first) { first = false; }
	              sql.append("AND modifiedBy LIKE '").append(valueObject.getModifiedBy()).append("%' ");
	          }

	          if (valueObject.getModifiedOn() != null) {
	              if (first) { first = false; }
	              sql.append("AND modifiedOn = '").append(valueObject.getModifiedOn()).append("' ");
	          }


	          sql.append("ORDER BY leave_id ASC ");

	          // Prevent accidential full table results.
	          // Use loadAll if all rows must be returned.
	          if (first)
	               searchResults = new ArrayList<Leave>();
	          else
	               searchResults = listQuery(conn, conn.prepareStatement(sql.toString()));

	          return searchResults;
	    }
	    protected int databaseUpdate(Connection conn, PreparedStatement stmt) throws SQLException {

	          int result = stmt.executeUpdate();

	          return result;
	    }
	   /* protected void singleQuery(Connection conn, PreparedStatement stmt, Leave valueObject) 
	          throws DAOException, SQLException {

	          ResultSet result = null;

	          try {
	              result = stmt.executeQuery();

	              if (result.next()) {

	                   valueObject.setLeaveId(result.getInt("leave_id")); 
	                   valueObject.setLeaveTypeId(result.getInt("leave_type_id")); 
	                   valueObject.setEmployeeId(result.getInt("employee_id")); 
	                   valueObject.setLeaveStartDate(result.getDate("leave_startDate")); 
	                   valueObject.setLeaveEndDate(result.getDate("leave_endDate")); 
	                   valueObject.setLeaveReason(result.getString("leave_reason")); 
	                   valueObject.setStatus(result.getString("status")); 
	                   valueObject.setRemarks(result.getString("remarks")); 
	                   valueObject.setRemainingLeaves(result.getInt("remaining_leaves")); 
	                   valueObject.setCreatedBy(result.getString("createdBy")); 
	                   valueObject.setCreatedOn(result.getTimestamp("createdOn")); 
	                   valueObject.setModifiedBy(result.getString("modifiedBy")); 
	                   valueObject.setModifiedOn(result.getTimestamp("modifiedOn")); 

	              } else {
	                    //System.out.println("Leave Object Not Found!");
	                    throw new DAOException("Leave Object Not Found!");
	              }
	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }
	    }*/

	    public List<Leave> listQuery(Connection conn, PreparedStatement stmt) throws SQLException {

	          ArrayList<Leave> searchResults = new ArrayList<Leave>();
	          ResultSet result = null;

	          try {
	              result = stmt.executeQuery();

	              while (result.next()) {
	                   Leave temp = createValueObject();

	                   temp.setLeaveId(result.getInt("leave_id")); 
	                   temp.setLeaveTypeId(result.getInt("leave_type_id")); 
	                   temp.setEmployeeId(result.getInt("employee_id")); 
	                   temp.setLeaveStartDate(result.getDate("leave_startDate")); 
	                   temp.setLeaveEndDate(result.getDate("leave_endDate")); 
	                   temp.setLeaveReason(result.getString("leave_reason")); 
	                   temp.setStatus(result.getString("status")); 
	                   temp.setRemarks(result.getString("remarks")); 
	                   temp.setRemainingLeaves(result.getInt("remaining_leaves")); 
	                   temp.setCreatedBy(result.getString("createdBy")); 
	                   temp.setCreatedOn(result.getTimestamp("createdOn")); 
	                   temp.setModifiedBy(result.getString("modifiedBy")); 
	                   temp.setModifiedOn(result.getTimestamp("modifiedOn")); 

	                   searchResults.add(temp);
	              }

	          } finally {
	              if (result != null)
	                  result.close();
	              if (stmt != null)
	                  stmt.close();
	          }

	          return (List<Leave>)searchResults;
	    }

		@Override
		public int countDaysByLeaveType(Connection conn,Leave valueObject)
				throws Exception {
			String sql = "select sum(remaining_leaves) as S from leaveapp.leave where leave_type_id="+
			valueObject.getLeaveTypeId()+" and employee_id="+valueObject.getEmployeeId()+" and status LIKE 'Approved'";
			System.out.println(sql);
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			int count=0;
			if(rs.next()){
				count = rs.getInt("S");
				System.out.println(count);
			}
			System.out.println(count);
			return count;
		}

		@Override
		public List<Leave> findLeaveByEmpId(Connection conn,int empid)
				throws Exception {
			// TODO Auto-generated method stub
			ArrayList<Leave> searchResults = new ArrayList<Leave>();
			String sql = "select * from leaveapp.leave where employee_id="+empid+" order by createdOn";
			Statement st = conn.createStatement();
			ResultSet result = null;
			result = st.executeQuery(sql);
			
			 while (result.next()) {
                 Leave temp = createValueObject();

                 temp.setLeaveId(result.getInt("leave_id")); 
                 temp.setLeaveTypeId(result.getInt("leave_type_id")); 
                 temp.setEmployeeId(result.getInt("employee_id")); 
                 temp.setLeaveStartDate(result.getDate("leave_startDate")); 
                 temp.setLeaveEndDate(result.getDate("leave_endDate")); 
                 temp.setLeaveReason(result.getString("leave_reason")); 
                 temp.setStatus(result.getString("status")); 
                 temp.setRemarks(result.getString("remarks")); 
                 temp.setRemainingLeaves(result.getInt("remaining_leaves")); 
                 temp.setCreatedBy(result.getString("createdBy")); 
                 temp.setCreatedOn(result.getTimestamp("createdOn")); 
                 temp.setModifiedBy(result.getString("modifiedBy")); 
                 temp.setModifiedOn(result.getTimestamp("modifiedOn")); 

                 searchResults.add(temp);
            }
			 return (List<Leave>)searchResults;
		}


	}

